package com.gxa.dao.impl;

import com.gxa.dao.CommunityDao;
import com.gxa.dto.CommunityDto;
import com.gxa.entity.Build;
import com.gxa.entity.Community;
import com.gxa.entity.extend.QueryAllCommunity;
import com.gxa.utils.DBConnection;
import com.gxa.vo.CommunityVo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CommunityDaoImpl implements CommunityDao {
    @Override
    public List<QueryAllCommunity> queryCommunity() throws SQLException {
        List<QueryAllCommunity> queryAllCommunities = new ArrayList<>();

        Connection connection = DBConnection.getConnection();
        String sql = "SELECT c.cid,c.cname,c.address,c.area,c.creator,c.managers,c.create_time,c.remark,b.totalBuildings,b.totalHouseholds " +
                "FROM community c " +
                "LEFT JOIN (SELECT SUM(unit_num) as totalHouseholds,COUNT(DISTINCT bname) as totalBuildings,community_id FROM build GROUP BY community_id) b on b.community_id = c.cid ";

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                String address = rs.getString("address");
                double area = rs.getDouble("area");
                String creator = rs.getString("creator");
                String managers = rs.getString("managers");
                Date create_time = rs.getDate("create_time");
                String remark = rs.getString("remark");
                int totalBuildings = rs.getInt("totalBuildings");
                int totalHouseholds = rs.getInt("totalHouseholds");

                QueryAllCommunity queryAllCommunity = new QueryAllCommunity(cid, cname, address, area, creator, managers, create_time, remark, totalBuildings, totalHouseholds);
                queryAllCommunities.add(queryAllCommunity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs, ps, connection);
        }

        return queryAllCommunities;
    }

    @Override
    public List<Community> selectAlLCommunity() throws SQLException {
        // 1、获取连接
        Connection connection = DBConnection.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Community> communities = new ArrayList<>();
        try {
            // 2、准备SQL
            String sql = "SELECT c.cid,c.cname,c.address,c.area,c.creator,c.managers,c.create_time,c.remark FROM community c";
            // 3、获取预编译对象，预编译SQL
            ps = connection.prepareStatement(sql);
            // 4、传入参数
            // 5、执行SQL语句
            rs = ps.executeQuery();
            // 6、处理查询结果集
            while (rs.next()) {
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                String address = rs.getString("address");
                double area = rs.getDouble("area");
                String creator = rs.getString("creator");
                String managers = rs.getString("managers");
                Date create_time = rs.getDate("create_time");
                String remark = rs.getString("remark");

                Community community = new Community(cid, cname, address, area, creator, managers, create_time, remark);
                communities.add(community);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7、释放资源
            DBConnection.close(rs, ps, connection);
        }

        return communities;
    }


    @Override
    public List<QueryAllCommunity> queryCommunity(Integer page, Integer limit) throws SQLException {
        List<QueryAllCommunity> commList = new ArrayList<>();
        Connection connection = DBConnection.getConnection();

        String sql = "SELECT c.cid,c.cname,c.address,c.area,c.creator,c.managers,c.create_time,c.remark,b.totalBuildings,b.totalHouseholds " +
                "FROM community c " +
                "LEFT JOIN (SELECT SUM(unit_num) as totalHouseholds,COUNT(DISTINCT bname) as totalBuildings,community_id FROM build GROUP BY community_id) b on b.community_id = c.cid LIMIT ?,?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1, (page - 1) * limit);
            ps.setInt(2, limit);
            rs = ps.executeQuery();

            while (rs.next()) {
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                String address = rs.getString("address");
                double area = rs.getDouble("area");
                String creator = rs.getString("creator");
                String managers = rs.getString("managers");
                Date create_time = rs.getDate("create_time");
                String remark = rs.getString("remark");
                int totalBuildings = rs.getInt("totalBuildings");
                int totalHouseholds = rs.getInt("totalHouseholds");

                QueryAllCommunity queryAllCommunity = new QueryAllCommunity(cid, cname, address, area, creator, managers, create_time, remark, totalBuildings, totalHouseholds);
                commList.add(queryAllCommunity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs, ps, connection);
        }

        return commList;
    }

    @Override
    public List<QueryAllCommunity> queryCommunity(Integer page, Integer limit, CommunityDto communityDto) throws SQLException {
        List<QueryAllCommunity> commList = new ArrayList<>();
        Connection connection = DBConnection.getConnection();

        StringBuilder sql = new StringBuilder("" +
                "SELECT c.cid,c.cname,c.address,c.area,c.creator,c.managers,c.create_time,c.remark,b.totalBuildings,b.totalHouseholds " +
                "FROM community c  " +
                "LEFT JOIN (SELECT SUM(unit_num) as totalHouseholds,COUNT(DISTINCT bname) as totalBuildings,community_id FROM build GROUP BY community_id) b on b.community_id = c.cid " +
                "WHERE 1=1 ");

        if (communityDto != null) {
            if (communityDto.getCid() != null) {
                sql.append(" and c.cid=?");
            }
            if (communityDto.getCname() != null && !"".equals(communityDto.getCname())) {
                sql.append(" and c.cname=?");
            }
            if (communityDto.getCreator() != null && !"".equals(communityDto.getCreator())) {
                sql.append(" and c.creator=?");
            }
            if (communityDto.getManagers() != null && !"".equals(communityDto.getManagers())) {
                sql.append(" and c.managers=?");
            }
        }

        sql.append(" limit ?,?");

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql.toString());

            int index = 1;
            if (communityDto != null) {
                if (communityDto.getCid() != null) {
                    ps.setInt(index, communityDto.getCid());
                    index++;
                }
                if (communityDto.getCname() != null && !"".equals(communityDto.getCname())) {
                    ps.setString(index, communityDto.getCname());
                    index++;
                }
                if (communityDto.getCreator() != null && !"".equals(communityDto.getCreator())) {
                    ps.setString(index, communityDto.getCreator());
                    index++;
                }
                if (communityDto.getManagers() != null && !"".equals(communityDto.getManagers())) {
                    ps.setString(index, communityDto.getManagers());
                    index++;
                }
            }


            ps.setInt(index, (page - 1) * limit);
            index++;
            ps.setInt(index, limit);
            index++;

            rs = ps.executeQuery();

            while (rs.next()) {
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                String address = rs.getString("address");
                double area = rs.getDouble("area");
                String creator = rs.getString("creator");
                String managers = rs.getString("managers");
                Date create_time = rs.getDate("create_time");
                String remark = rs.getString("remark");
                int totalBuildings = rs.getInt("totalBuildings");
                int totalHouseholds = rs.getInt("totalHouseholds");

                QueryAllCommunity queryAllCommunity = new QueryAllCommunity(cid, cname, address, area, creator, managers, create_time, remark, totalBuildings, totalHouseholds);
                commList.add(queryAllCommunity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs, ps, connection);
        }

        return commList;
    }

    @Override
    public List<CommunityVo> selectAlLCommunityByPageAndDto(int page, int limit, CommunityDto communityDto) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<CommunityVo> communityList = new ArrayList<>();
        try {
            // 1、注册驱动、获取连接
            connection = DBConnection.getConnection();
            // 2、准备SQL
            StringBuffer sql = new StringBuffer("SELECT c.cid,c.cname,c.address,c.area,c.creator,c.managers,c.create_time,c.remark,b.totalBuildings,b.totalHouseholds\n" +
                    "                FROM community c\n" +
                    "                LEFT JOIN (SELECT SUM(unit_num) as totalHouseholds,COUNT(DISTINCT bname) as totalBuildings,community_id FROM build GROUP BY community_id) b on b.community_id = c.cid\n" +
                    "            WHERE 1=1");


            // 2.1、动态拼接SQL，方便确定传递哪些参数
            if (communityDto != null) {
                if (communityDto.getCid() != null) {
                    sql.append(" and c.cid=?");
                }
                if (communityDto.getCname() != null && !"".equals(communityDto.getCname())) {
                    sql.append(" and c.cname=?");
                }
                if (communityDto.getCreator() != null && !"".equals(communityDto.getCreator())) {
                    sql.append(" and c.creator=?");
                }
                if (communityDto.getManagers() != null && !"".equals(communityDto.getManagers())) {
                    sql.append(" and c.managers=?");
                }
            }
            sql.append(" limit ?,?");

            // 3、获取预编译对象，预编译SQL
            ps = connection.prepareStatement(sql.toString());

            // 4、开始按下标传递参数
            int index = 1;
            if (communityDto != null) {
                if (communityDto.getCid() != null) {
                    ps.setInt(index, communityDto.getCid());
                    index++;
                }
                if (communityDto.getCname() != null && !"".equals(communityDto.getCname())) {
                    ps.setString(index, communityDto.getCname());
                    index++;
                }
                if (communityDto.getCreator() != null && !"".equals(communityDto.getCreator())) {
                    ps.setString(index, communityDto.getCreator());
                    index++;
                }
                if (communityDto.getManagers() != null && !"".equals(communityDto.getManagers())) {
                    ps.setString(index, communityDto.getManagers());
                    index++;
                }
            }


            ps.setInt(index, (page - 1) * limit);
            index++;
            ps.setInt(index, limit);
            index++;

            rs = ps.executeQuery();
            // 6、处理查询结果集
            while (rs.next()){
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                String address = rs.getString("address");
                double area = rs.getDouble("area");
                String creator = rs.getString("creator");
                String managers = rs.getString("managers");
                Date create_time = rs.getDate("create_time");
                String remark = rs.getString("remark");
                int totalBuildings = rs.getInt("totalBuildings");
                int totalHouseholds = rs.getInt("totalHouseholds");

                CommunityVo communityVo = new CommunityVo(cid, cname, address, area, creator, managers, create_time, remark, totalBuildings, totalHouseholds);
                communityList.add(communityVo);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7、释放资源
            DBConnection.close(rs,ps,connection);
        }

        return communityList;
    }

    @Override
    public Integer count() throws SQLException {
        Connection connection = DBConnection.getConnection();
        String sql = "SELECT COUNT(c.cid) as total " +
                "FROM community c " +
                "LEFT JOIN (SELECT SUM(unit_num) as totalHouseholds,COUNT(DISTINCT bname) as totalBuildings,community_id FROM build GROUP BY community_id) b on b.community_id = c.cid ";

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();

            if (rs.next()) {
                int total = rs.getInt("total");
                return total;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs, ps, connection);
        }

        return 0;
    }

    @Override
    public Integer count(CommunityDto communityDto) throws SQLException {
        Connection connection = DBConnection.getConnection();
        StringBuilder sql = new StringBuilder("SELECT COUNT(c.cid) as total " +
                "FROM community c " +
                "LEFT JOIN (SELECT SUM(unit_num) as totalHouseholds,COUNT(DISTINCT bname) as totalBuildings,community_id FROM build GROUP BY community_id) b on b.community_id = c.cid WHERE 1=1");


        if (communityDto != null) {
            if (communityDto.getCid() != null) {
                sql.append(" and c.cid=?");
            }
            if (communityDto.getCname() != null && !"".equals(communityDto.getCname())) {
                sql.append(" and c.cname=?");
            }
            if (communityDto.getCreator() != null && !"".equals(communityDto.getCreator())) {
                sql.append(" and c.creator=?");
            }
            if (communityDto.getManagers() != null && !"".equals(communityDto.getManagers())) {
                sql.append(" and c.managers=?");
            }
        }

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql.toString());

            int index = 1;

            if (communityDto != null) {
                if (communityDto.getCid() != null) {
                    ps.setInt(index, communityDto.getCid());
                    index++;
                }
                if (communityDto.getCname() != null && !"".equals(communityDto.getCname())) {
                    ps.setString(index, communityDto.getCname());
                    index++;
                }
                if (communityDto.getCreator() != null && !"".equals(communityDto.getCreator())) {
                    ps.setString(index, communityDto.getCreator());
                    index++;
                }
                if (communityDto.getManagers() != null && !"".equals(communityDto.getManagers())) {
                    ps.setString(index, communityDto.getManagers());
                    index++;
                }
            }

            rs = ps.executeQuery();
            if (rs.next()) {
                int total = rs.getInt("total");
                return total;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs, ps, connection);
        }

        return 0;
    }

    @Override
    public void add(Community community) throws SQLException {
        Connection connection = DBConnection.getConnection();
        String sql = "INSERT INTO community(cid,cname,address,area,creator,managers,create_time,remark) VALUES(?,?,?,?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1, community.getCid());
            ps.setString(2, community.getCname());
            ps.setString(3, community.getAddress());
            ps.setDouble(4, community.getArea());
            ps.setString(5, community.getCreator());
            ps.setString(6, community.getManagers());
            //将java.util.Date 转换为 java.sql.Date
            long time = community.getCreateTime().getTime();
            ps.setDate(7, new java.sql.Date(time));
            ps.setString(8, community.getRemark());

            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(null, ps, connection);
        }

    }

    @Override
    public Community queryByID(Integer cid) throws SQLException {
        Connection connection = DBConnection.getConnection();
        String sql = "SELECT * FROM community WHERE cid =?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1, cid);
            rs = ps.executeQuery();

            if (rs.next()) {
                String cname = rs.getString("cname");
                String address = rs.getString("address");
                double area = rs.getDouble("area");
                String creator = rs.getString("creator");
                String managers = rs.getString("managers");
                //左边java.util.Date = 右边 java.sql.Date
                java.util.Date createTime = rs.getDate("create_time");
                String remark = rs.getString("remark");

                Community community = new Community(cid, cname, address, area, creator, managers, createTime, remark);
                return community;

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs, ps, connection);
        }

        return null;
    }

    @Override
    public void update(Community community) throws SQLException {
        Connection connection = DBConnection.getConnection();
        String sql = "UPDATE community SET cname=?,address=?,area=?,creator=?,managers=?,create_time=?,remark=? WHERE cid=? ";
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, community.getCname());
            ps.setString(2, community.getAddress());
            ps.setDouble(3, community.getArea());
            ps.setString(4, community.getCreator());
            ps.setString(5, community.getManagers());
            //将java.util.Date 转换为 java.sql.Date
            long time = community.getCreateTime().getTime();
            ps.setDate(6, new java.sql.Date(time));
            ps.setString(7, community.getRemark());
            ps.setInt(8, community.getCid());

            ps.execute();


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(null, ps, connection);
        }


    }

    @Override
    public void delete(Integer cid) throws SQLException {
        Connection connection = DBConnection.getConnection();
        String sql = "DELETE FROM community WHERE cid=? ";

        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1, cid);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(null, ps, connection);
        }

    }

    @Override
    public int updateRemarkInCommunity(Community community) {
        Connection connection = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            // 1、注册驱动
            // 2、获取连接
            connection = DBConnection.getConnection();
            System.out.println("Dao层的conn===>"+connection);

            String sql = "UPDATE community SET remark = ? WHERE cid = ?";
            // 3、获取数据库预编译对象
            ps = connection.prepareStatement(sql);
            // 传入参数
            ps.setString(1, community.getRemark());
            ps.setInt(2, community.getCid());
            // 4、执行SQL语句
            count = ps.executeUpdate();
            // 5、有查询操作需要处理查询结果集
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6、释放资源
            DBConnection.close(null,ps,null);
        }

        return count;
    }
}
